package jmind.core.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.sql.DataSource;

import jmind.base.util.DataUtil;

import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.KeyHolder;

public abstract class BaseJdbc {

    /**
     *  0 主库
     */
    public static final int MASTER_SOURCE = 0;

    /** slave **/
    public static int SLAVE_SOURCE = 1;

    public static final String LAST_ID_SQL = "select last_insert_id()";

    public abstract JdbcTemplate getJdbc(int source);

    /**
     * 插入数据并返回自增id
     * @param sql
     * @param keyName 需返回值的字段名
     * @param args
     * @return
     */
    public Number insertAndReturnKey(final String sql, final String keyName, final Object... args) {
        Number key = 0;
        final KeyHolder keyHolder = new GeneratedKeyHolder();
        final Integer affectRows = getJdbc(MASTER_SOURCE).update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(final Connection connection) throws SQLException {
                final PreparedStatement ps = connection.prepareStatement(sql, new String[] { keyName });
                for (int i = 0; i < args.length; i++) {
                    ps.setObject(i + 1, args[i]);
                }
                return ps;
            }
        }, keyHolder);
        if (affectRows > 0) {
            key = keyHolder.getKey();
        }
        return key;
    }

    public Number insertAndReturnKey(SimpleJdbcInsert jdbcInsert, Object obj) {
        // new SimpleJdbcInsert(this.getJdbc(defaultSource).getDataSource())
        //  .withTableName("userbasic").usingGeneratedKeyColumns("id");
        SqlParameterSource parameters = new BeanPropertySqlParameterSource(obj);
        return jdbcInsert.executeAndReturnKey(parameters);
    }

    public int insert(SimpleJdbcInsert jdbcInsert, Object obj) {
        //   new SimpleJdbcInsert(this.getJdbc(defaultSource).getDataSource())
        //  .withTableName("userbasic");
        SqlParameterSource parameters = new BeanPropertySqlParameterSource(obj);

        int i = jdbcInsert.execute(parameters);
        //  System.out.println(jdbcInsert.getTableName() + "==" + jdbcInsert.getInsertString());
        return i;
    }

    public int exeAndReturnLastId(String sql) {
        DataSource dataSource = getJdbc(MASTER_SOURCE).getDataSource();
        Connection con = DataSourceUtils.getConnection(dataSource);
        Statement stmt = null;
        int lastId = 0;
        try {
            stmt = con.createStatement();
            DataSourceUtils.applyTransactionTimeout(stmt, dataSource);
            // Increment the sequence column...

            stmt.executeUpdate(sql);
            // Retrieve the new max of the sequence column...
            ResultSet rs = stmt.executeQuery(LAST_ID_SQL);
            try {
                if (!rs.next()) {
                    throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update");
                }
                lastId = rs.getInt(1);
            } finally {
                JdbcUtils.closeResultSet(rs);
            }

        } catch (SQLException ex) {
            throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", ex);
        } finally {
            JdbcUtils.closeStatement(stmt);
            DataSourceUtils.releaseConnection(con, dataSource);
        }
        return lastId;
    }

    /**
     * 修改操作
     * @param sql
     * @param args
     * @return
     */
    public int update(String sql, Object... args) {

        return getJdbc(MASTER_SOURCE).update(sql, args);
    }

    /**
     * 删除
     * 2014-2-18 
     * @param tableName
     * @param keyName
     * @param key
     * @return
     */
    public int delete(String tableName, String keyName, Object key) {
        return getJdbc(MASTER_SOURCE).update("delete from " + tableName + " where " + keyName + "=?", key);
    }

    /**
     * 
     * 2013-7-2 
     * @param tableName 表名
     * @param key 修改条件字段名
     * @param val 修改条件值
     * @param mark 需要修改的内容
     * @return
     */
    public int update(String tableName, String key, Object val, Map<String, Object> mark) {
        if (DataUtil.isEmpty(mark))
            return 0;
        Object[] args = new Object[mark.size() + 1];
        StringBuilder sql = new StringBuilder("update " + tableName + " set ");
        int i = 0;
        for (Entry<String, Object> entry : mark.entrySet()) {
            sql.append(entry.getKey()).append("= ?,");
            args[i++] = entry.getValue();
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" where ").append(key).append("=?");
        args[i] = val;
        return getJdbc(MASTER_SOURCE).update(sql.toString(), args);
    }

    /**
     *  得到某列的值
     * @param <T>
     * @param source 数据库源
     * @param sql
     * @param requiredType
     * @param args 参数
     * @return
     */
    public <T> T findColumValue(int source, String sql, Class<T> requiredType, Object... args) {
        try {
            return getJdbc(source).queryForObject(sql, requiredType, args);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public <T> List<T> findColumValues(int source, String sql, Class<T> requiredType, Object... args) {
        return getJdbc(source).queryForList(sql, requiredType, args);
    }

    /**
     * 得到int 值
     * @param source 数据库源
     * @param sql
     * @param args 参数
     * @return
     */
    public int findForInt(int source, String sql, Object... args) {
        try {
            return getJdbc(source).queryForObject(sql,Integer.class,args);
        } catch (EmptyResultDataAccessException e) {
            return 0;
        }
    }

    /**
     * 查找一条记录
     * @param source 数据库源
     * @param sql
     * @param args 参数
     * @return
     */
    public Map<String, Object> findForMap(int source, String sql, Object... args) {
        try {
            return getJdbc(source).queryForMap(sql, args);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

    /**
     * 查找多条记录
     * @param source 数据库源
     * @param sql
     * @param args 参数
     * @return
     */
    public List<Map<String, Object>> findForList(int source, String sql, Object... args) {
        return getJdbc(source).queryForList(sql, args);
    }

}
